package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import Model.Booking;


public class DBBooking implements IFDBBooking {
	private static Connection con;
	Statement stmt;
	public DBBooking() 
	{
		con = DBCon.getInstance().getDBCon();
	}
//Create a new Booking
	@Override
	public int createBooking(int coID,String date_time,int cID,int chID, String sStart, String sEnd,int iID)
	{int bID=0;
		try{
			bID=GetMax.getMaxId("Select max(bID) from Booking")+1;//Gets the maximum bID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Booking(bID, coID, date, cID, chID, sStart, sEnd, iID)  VALUES('"+bID+"','"+coID+"','"+date_time+"','"+cID+"','"+chID+"','"+sStart+"','"+sEnd+"','"+iID+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
		return bID;
	}
//Delete a Booking by it's ID
	@Override
	public boolean deleteBooking(int bID)
	{	 boolean b=false;
		try{
			String query="DELETE FROM Booking WHERE bID = '"+bID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
	         b = true;
		}
		catch(Exception e)
		{e.printStackTrace();
		}
		return b;
	}
	//Update Booking by choice
	@Override
	public boolean updateBooking(Booking g,int choice)
	{ 
		boolean exec =false;
		String query = "";
		Booking suObj = g;
	
              
		switch(choice){
	case 0: // Court Id Update
		query="UPDATE Booking SET "+
			 	  "coID ='"+ suObj.getCoID()+"'"+
			          " WHERE bID = '"+ suObj.getbID()+ "'";
			 	 exec =true;
			 	 break;
	case 1://Date Update
		query="UPDATE Booking SET "+
			 	  "date ='"+ suObj.getDate_time()+"'"+
			          " WHERE bID = '"+ suObj.getbID()+ "'";
			 	 exec =true;
			 	 break;
	case 2://Client Update
		query="UPDATE Booking SET "+
			 	  "cID ='"+ suObj.getcID()+"'"+
			          " WHERE bID = '"+ suObj.getbID()+ "'";
			 	 exec =true;
			 	 break;
	case 3://Couch Update
		query="UPDATE Booking SET "+
			 	  "chID ='"+ suObj.getchID()+"'"+
			          " WHERE bID = '"+ suObj.getbID()+ "'";
			 	 exec =true;
			 	 break;
	case 4://Update Session Start Time
		query="UPDATE Booking SET "+
				"sStart ="+ suObj.getsStart()+"'"+
				"WHERE bID = '"+ suObj.getbID()+ "'";
		exec=true;
		break;
	case 5://Update Session End Time
		query="UPDATE Bookin SET "+
				"sEnd ="+ suObj.getsEnd()+"'"+
				"WHERE bID = '"+ suObj.getbID()+ "'";
		exec=true;
		
		break;
	case 6://Update All
		 query="UPDATE Booking SET "+
			 	  "coID ='"+ suObj.getCoID()+"', "+
			 	  "date ='"+ suObj.getDate_time()+"', "+
			 	 "cID ='"+ suObj.getcID()+"', "+
			 	"chID ='"+ suObj.getchID() + "'" +
			          " WHERE bID = '"+ suObj.getbID() + "'";
		 exec=true;
		 break;
     //Updating the Invoice is not possible
	/*case 7:
			query="UPDATE Booking SET "+
					"iID = '"+ suObj.getiID() + "'"+
					"WHERE bID = '" + suObj.getbID() + "'";
			exec=true;
			break;*/
	}
		while(exec)
		{exec =false;
			try{
	      	  Statement stmt = con.createStatement();
	      	  stmt.setQueryTimeout(5);
	      	  stmt.executeUpdate(query);
	      	  stmt.close();
	      	  
	        }
	        catch(Exception e)
	        {e.printStackTrace();}
			break;}
	return exec;
	}
//The Query used to call information from the Table Booking in the DB
	private String buildQuery()
	{
	    String query="SELECT bID,coID,date,cID,chID,sStart,sEnd,iID  FROM Booking";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
//Returns all Bookings from the DB
	@Override
	public ArrayList<Booking> miscWhere()
	{
            ResultSet results;
	    ArrayList<Booking> list = new ArrayList<Booking>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Booking bObj = new Booking();
		         bObj = buildBooking(results);	
                 list.add(bObj);	
		}//end while
                 stmt.close();     
                 
			
		}//Close try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
//Method that returns a Booking object from DB
	@Override
	public Booking findBooking(int bID)
	{
		ResultSet results;
		Booking bObj = new Booking();
                
	        String query =  buildQuery() + " WHERE bID ="+"'"+bID+"'";
               // System.out.println(query);
		try{ // Query the DB for all Bookings and add them to the result set
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            bObj = buildBooking(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Booking was found
                            bObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return bObj;
	}
	
	@Override
	public ArrayList<Booking> findAllbyDate(String date)
	{
            ResultSet results;
	    ArrayList<Booking> list = new ArrayList<Booking>();	
		
	    String query =  buildQuery() + " WHERE Date ="+"'" + date +"'";
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Booking bObj = new Booking();
		         bObj = buildBooking(results);	
                 list.add(bObj);	
		}//end while
                 stmt.close();     
                 
			
		}//Close try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	@Override
	public Booking findBookingByDate(String date)
	{
		ResultSet results;
		Booking bObj = new Booking();
                
	        String query =  buildQuery() + " WHERE date ="+"'"+date+"'";
               // System.out.println(query);
		try{ // Query the DB for all Bookings and add them to the result set
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            bObj = buildBooking(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Booking was found
                            bObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return bObj;
	}
	
	
	
	@Override
	public Booking findBookingByDateTime(String date, String sStart,String sEnd)
	{
		ResultSet results;
		Booking bObj = new Booking();
                
	        String query =  buildQuery() + " WHERE date ="+"'"+date+"'" +" AND sStart>='"+sStart+"' "+"AND sEnd<='"+sEnd+"'";
               // System.out.println(query);
		try{ // Query the DB for all Bookings in the date and time range and add them to the result set
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            bObj = buildBooking(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Booking was found
                            bObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		//System.out.println("Query exception: "+e);
	 	}
		return bObj;
	}
	
	private Booking buildBooking(ResultSet results)
    {   Booking suObj = new Booking();
        try{ // the columns from the table Booking  are used to fill the information in the new booking object
              suObj.setbID(results.getInt("bID"));
              suObj.setCoID(results.getInt("coID"));
              suObj.setDate_time(results.getString("Date"));
              suObj.setcID(results.getInt("cID"));
              suObj.setchID(results.getInt("chID"));
              suObj.setsStart(results.getString("sStart"));
              suObj.setsEnd(results.getString("sEnd"));
              suObj.setiID(results.getInt("iID"));
              
        }
       catch(Exception e)
       {
          // System.out.println("error in building the Booking object");
       }
       return suObj;
}


	
}